INSERT INTO provider_behaivor_history_month 
    (provider_id, profit, roi, last_update)

-- se calculan los ganancias por item
WITH DetalleGanancias AS (
    SELECT 
        p.provider_id,
        p.id AS purchase_id,
        DATE_FORMAT(p.purchase_date, '%Y-%m-01') as mes,
        -- Costo base de la mercancía comprada
        SUM(
        pvi.qty * (
                    pvi.unit_price + 
                    CASE 
                        WHEN (pvi.qty - pvi.returned_qty) <= 0 THEN 0
                        ELSE (pvi.cogs_row_amount / (pvi.qty - pvi.returned_qty))
                    END
                )
        ) costo_factura_mercancia,
        -- Fórmula PHP: (Venta) - (Costo Unitario + Costo Adicional por Fila)
        SUM(
        ((svi.qty - svi.returned_qty) * svi.price) - 
            (
                svi.qty * (
                    pvi.unit_price + 
                    CASE 
                        WHEN (pvi.qty - pvi.returned_qty) <= 0 THEN 0
                        ELSE (pvi.cogs_row_amount / (pvi.qty - pvi.returned_qty))
                    END
                )
            )
        ) AS ganancias_items
    FROM purchase p
    INNER JOIN purchase_vs_item pvi ON p.id = pvi.purchaseId
    INNER JOIN sale_vs_item svi ON pvi.id = svi.purchase_vs_itemId
    WHERE p.deleted = 0 
      AND pvi.deleted = 0 
      AND svi.deleted = 0
    GROUP BY p.provider_id, p.id, DATE_FORMAT(p.purchase_date, '%Y-%m-01')
)
-- se calculan e insertan los datos
SELECT 
    d.provider_id,
    SUM(COALESCE(d.ganancias_items, 0)) AS profit,
    CASE 
        WHEN SUM(d.costo_factura_mercancia) > 0 
        THEN (SUM(COALESCE(d.ganancias_items, 0)) / 
              SUM(d.costo_factura_mercancia) * 100) 
        ELSE 0 
    END AS roi,
    LAST_DAY(d.mes) AS last_update
FROM DetalleGanancias d
GROUP BY d.provider_id, d.mes;

